CREATE TABLE PRODUCTS(
PRODUCT_ID NUMBER NOT NULL,
PRODUCT_IMG BLOB,
PRODUCT_NAME VARCHAR2(50),
PRODUCT_DESCRIPTION VARCHAR(500),
PRODUCT_PRICE FLOAT,
PRODUCT_TIMESTAMP Date,
CONSTRAINT PRODUCT_PK PRIMARY KEY(PRODUCT_ID)
);

CREATE TABLE CATEGORIES(
CATEGORY_ID NUMBER NOT NULL,
CATEGORY_NAME VARCHAR2(50),
CATEGORY_UP NUMBER,
CONSTRAINT CATEGORIES_PK PRIMARY KEY(CATEGORY_ID),
CONSTRAINT CATEGORIES_FK FOREIGN KEY(CATEGORY_UP) 
            REFERENCES CATEGORIES(CATEGORY_ID));
            
CREATE TABLE USERS(
USER_ID NUMBER NOT NULL,
USERNAME VARCHAR2(20) NOT NULL,
USER_PASSWORD VARCHAR2(20) NOT NULL,
USER_EMAIL VARCHAR2(40) NOT NULL,
USER_CONTACTS VARCHAR2(400),
CONSTRAINT USER_PK PRIMARY KEY(USER_ID)
);

CREATE TABLE USER_BOUGHT(
USER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
CONSTRAINT USER_BOUGHT_PK PRIMARY KEY(USER_ID),
CONSTRAINT USER_BOUGHT_FK FOREIGN KEY(PRODUCT_ID) 
            REFERENCES PRODUCTS(PRODUCT_ID));

CREATE TABLE USER_SALES(
USER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
CONSTRAINT USER_SALES_PK PRIMARY KEY(USER_ID),
CONSTRAINT USER_SALES_FK FOREIGN KEY(PRODUCT_ID) 
            REFERENCES PRODUCTS(PRODUCT_ID));


CREATE TABLE USER_LIKED_CATEGORIES(
USER_ID NUMBER NOT NULL,
CATEGORY_ID NUMBER NOT NULL,
CONSTRAINT USER_LIKED_CATEGORIES_PK PRIMARY KEY(USER_ID),
CONSTRAINT USER_LIKED_CATEGORIES FOREIGN KEY(CATEGORY_ID) 
            REFERENCES CATEGORIES(CATEGORY_ID));
                  
CREATE SEQUENCE USER_SEQUENCE;
CREATE SEQUENCE PRODUCT_SEQUENCE;
CREATE SEQUENCE CATEGORY_SEQUENCE;

CREATE TRIGGER before_insert_user
BEFORE INSERT ON USERS FOR EACH ROW
BEGIN
  SELECT USER_SEQUENCE.NEXTVAL
  INTO :new.USER_ID
  FROM dual;
END;
/
CREATE TRIGGER before_insert_product
BEFORE INSERT ON PRODUCTS FOR EACH ROW
BEGIN
  SELECT USER_SEQUENCE.NEXTVAL
  INTO :new.PRODUCT_ID
  FROM dual;
END;
/
CREATE TRIGGER before_insert_categories
BEFORE INSERT ON categories FOR EACH ROW
BEGIN
  SELECT CATEGORY_SEQUENCE.NEXTVAL
  INTO :new.CATEGORY_ID
  FROM dual;
END;
/

ALTER TABLE PRODUCTS
ADD (category_id NUMBER);

ALTER TABLE PRODUCTS
ADD CONSTRAINT category_fk FOREIGN KEY(CATEGORY_ID) 
            REFERENCES CATEGORIES (CATEGORY_ID);

INSERT INTO USERS(USERNAME,USER_PASSWORD,USER_EMAIL)
VALUES('ADMIN','ADMIN','ADMIN@ADMIN');

INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Bitova tehnika',NULL);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Gotvarski uredi',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Kafemashini',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Otoplitelni uredi',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Boileri',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Peralni',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Prahosmukachki',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Hladilnici',1);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Drugi uredi',1);

INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Dom i gradina',NULL);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Za banqta',10);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Za gradinata',10);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Za kuhnqta',10);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Instrumenti i mashini',10);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Drugi za doma',10);

INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Sport',NULL);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Lov i ribolov',16);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Obleklo',16);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Sportni uredi',16);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Turizum',16);
INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Drugi sportni',16);
 
 INSERT INTO CATEGORIES(CATEGORY_NAME,CATEGORY_UP) VALUES('Drugi',NULL);

